package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.bo.PhysicalCar;
import edu.byu.intex2.data.bo.Warranty;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * Warranty Data Access Object
 * @author Brady
 */
public class WarrantyDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static WarrantyDAO instance = null;

    /** Creates a new instance of WarrantyDAO */
    private WarrantyDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized WarrantyDAO getInstance() {
        if (instance == null) {
            instance = new WarrantyDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new warranty in the database */
    public Warranty create(String id) throws DataException {
        Warranty warranty = new Warranty(id);
        warranty.setObjectAlreadyInDB(false);
        Cache.getInstance().put(warranty.getId(), warranty);
        return warranty;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing warranty from the database     */
    public Warranty read(String id) throws DataException {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (Warranty) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(id, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing warranty from the database
     * @return warranty
     */
    synchronized Warranty read(String id, Connection conn) throws Exception {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (Warranty) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM warranty where id=?");

        try {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("Warranty with id '" + id + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a warranty object from a record  */
    synchronized Warranty readRecord(ResultSet rs, Connection conn) throws Exception {
        if (Cache.getInstance().containsKey(rs.getString("id"))) {
            return (Warranty) Cache.getInstance().get(rs.getString("id"));
        }
        // Create new warranty object
        Warranty warranty = new Warranty(rs.getString("id"));

        // Warranty is already in DB
        warranty.setObjectAlreadyInDB(true);

        // Put warranty in cache
        Cache.getInstance().put(warranty.getId(), warranty);

        // Set Warranty Attributes
        warranty.setMiles(rs.getInt("miles"));
        warranty.setMonths(rs.getInt("months"));
        warranty.setType(rs.getString("warr_type"));

        // Warranty is dirty and needs to be saved to DB
        warranty.setDirty(false);

        return warranty;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing warranty in the database */
    public void save(Warranty warranty) throws DataException {
        // get a connection from the pool, save all of the sales first using the other warranty method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(warranty, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the warranty database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the warranty object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a warranty in the database */
    void save(Warranty warranty, Connection conn) throws Exception {
        // put the warranty object to cache
        Cache.getInstance().put(warranty.getId(), warranty);

        // decide whether to update or insert into the database
        if (warranty.isObjectAlreadyInDB() && warranty.isDirty()) {
            update(warranty, conn);
        } else if (warranty.isDirty()) {
            insert(warranty, conn);
        }//if
    }

    /** External method to update or create a warranty in the database without committing connection */
    public void saveNoCommit(Warranty warranty, Connection conn, Boolean alreadyInDB) throws Exception {
        // decide whether to update or insert into the database
        if (alreadyInDB) {
            update(warranty, conn);
        } else {
            insert(warranty, conn);
        }
    }

    /** Saves an existing warranty to the database */
    private void update(Warranty warranty, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE warranty SET miles=?, months=?, warr_type=?  WHERE id=?");
        try {
            // Get Warranty Attributes and add them to the statement
            stmt.setInt(1, warranty.getMiles());
            stmt.setInt(2, warranty.getMonths());
            stmt.setString(3, warranty.getType());
            stmt.setString(4, warranty.getId());
            stmt.execute();

            // Warranty is clean!
            warranty.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new warranty into the database */
    private void insert(Warranty warranty, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO warranty (id, miles, months, warr_type) VALUES (?, ?, ?, ?)");
        try {
            // Set Warranty Attributes
            stmt.setString(1, warranty.getId());
            stmt.setInt(2, warranty.getMiles());
            stmt.setInt(3, warranty.getMonths());
            stmt.setString(4, warranty.getType());
            stmt.execute();

            warranty.setObjectAlreadyInDB(true); // now in DB
            warranty.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }


    /** Inserts a new warranty into the database */
    public void addWarrantyToPhysicalCar(Warranty warranty, PhysicalCar pc) throws Exception {
        // Grab a Connection
        Connection conn = ConnectionPool.getInstance().get();

        PreparedStatement stmt = conn.prepareStatement("INSERT INTO physical_car_warranty (physical_car_id, warranty_id) VALUES (?, ?)");
        try {
            // Set Warranty Attributes
            stmt.setString(1, pc.getId());
            stmt.setString(2, warranty.getId());
            stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
            ConnectionPool.getInstance().release(conn);
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(Warranty warranty) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    /**
     * Internal method to get all warranty for a phsysical car from the database
     * @return warranties
     */
    public List<Warranty> getByPhysicalCar(String pcid) throws Exception {
        // Grab Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM physical_car_warranty pcw JOIN warranty w ON pcw.warranty_id = w.id where pcw.physical_car_id=?");

        List<Warranty> warranties = new ArrayList<Warranty>();
        try {
            stmt.setString(1, pcid);
            ResultSet rs = stmt.executeQuery(); // execute query
            while (rs.next()) {
                // Read the record with the result set
                warranties.add(readRecord(rs, conn));
            }//if
            return warranties;
//            throw new DataException("Warranty with id '" + id + "' not found.");
        } finally {
            stmt.close();
            ConnectionPool.getInstance().release(conn);
        }
    }//read
}
